# ==============================================================================
#
# dbtools.pm
#
# Account maint database functions
#
# $Id: dbtools.pm,v 1.6 2005/08/25 22:38:26 dyoung2 Exp $
#
# Darren Young [darren.young@gsb.uchicago.edu]
#
# ==============================================================================
#
# ChangeLog
#
# $Log: dbtools.pm,v $
# Revision 1.6  2005/08/25 22:38:26  dyoung2
#   * Standardized BEGIN headers.
#
# Revision 1.5  2005/04/26 22:30:19  dyoung2
# update
#
# Revision 1.4  2005/04/11 19:46:42  dyoung2
#   * NOT counting the rows first now.
#
# Revision 1.3  2005/04/11 18:39:53  dyoung2
#   * Added debugging to getDBPersonID().
#
# Revision 1.2  2005/04/11 18:22:25  dyoung2
#   * Added getDBPersonID().
#
# Revision 1.1  2005/04/11 16:55:58  dyoung2
#   * Initial version.
#
# ==============================================================================

my $cvsid = '$Id: dbtools.pm,v 1.6 2005/08/25 22:38:26 dyoung2 Exp $';
my @cvsinfo = split( ' ', $cvsid );
our $NAME    = File::Basename::basename($0);
our $VERSION = $cvsinfo[2];

# ------------------------------------------------------------------------------
#                             B E G I N
# ------------------------------------------------------------------------------
BEGIN {

    # Pragmas
    use strict;

    # "Standard" modules we use
    use FindBin;
    use Config::Simple;
    use lib "$FindBin::Bin/../lib";

    # Standard account maint modules to use
    use logging;
    use errcodes;
    use utils;
    
    # Modules that this module uses
    use DBI;

}


# ------------------------------------------------------------------------------ 
#                             V A R I A B L E S
# ------------------------------------------------------------------------------ 
our $LOGFILE    = "$FindBin::Bin/../log/acctmgr.log" unless($LOGFILE); 
our $CONFIGFILE = "$FindBin::Bin/../etc/acctmgr.cfg" unless($CONFIGFILE); 


# ------------------------------------------------------------------------------
#                             C O N F I G   F I L E
# ------------------------------------------------------------------------------
our $CFG = new Config::Simple( filename => $CONFIGFILE ) or die "$!\n" unless($CFG);


# ==============================================================================
# NAME        : getDBLoginRecord
# DESCRIPTION : returns all information for a given login
# ARGUMENTS   : string(login)
# RETURN      : hash(dbrecord) or FALSE
# NOTES       : fields in view are
#             : person_id, user_name, last_name, first_name,
#             : acct_class, insert_ts, change_ts
# ==============================================================================
sub getDBLoginRecord {
    my ($login) = @_;
    my $name = "getDBLoginRecord";
    my ( $package, $filename, $line ) = caller; 
    debug("$name: entering with args @_");
    debug("$name: called from package->$package, filename->$filename, line->$line");      

    my $found;
    my %dbrecord;

    # default to no values for this
    $dbrecord{'PERSON_ID'} = "";
    $dbrecord{'USER_NAME'}  = "";
    $dbrecord{'LAST_NAME'}  = "";
    $dbrecord{'FIRST_NAME'} = "";
    $dbrecord{'ACCT_CLASS'} = "";
    $dbrecord{'INSERT_TS'}  = "";
    $dbrecord{'CHANGE_TS'}  = "";

    # set this just in case it's not, otherwise DBD::Ingres fails
    $ENV{"II_SYSTEM"} = "/opt";

    my $vnode  = $CFG->param("database.vnode");
    my $dbname = $CFG->param("database.dbname");
    my $view   = $CFG->param("database.acctview");
    my $dbconn = "DBI:Ingres:" . $vnode . "::" . $dbname;

    debug("$name: using dbconn string -> $dbconn");

    debug("$name: attempting to connect to database");
    my $dbh = DBI->connect( $dbconn ) || die "database connect failed: $DBI::errstr";

    my $sql = qq(SELECT * FROM $view WHERE user_name = ?);
    debug("$name: preparing sql statement");
    debug("$name: $sql");

    my $sth = $dbh->prepare($sql) or die("failed on sql prepare: $DBI::errstr");

    debug("$name: executing sql (? = $login)");
    $sth->execute($login);


    my @data = $sth->fetchrow_array();
    debug("$name: found " . $sth->rows() . " rows in table for query");

    # if there are more than 1 rows, shit out
    if ( $sth->rows() > 1 ) {
        print "asshole, too many rows from query!\n";
        return(FALSE);
    } 

    # populate the hash with the returned values
    $dbrecord{'PERSON_ID'}  = $data[0];
    $dbrecord{'USER_NAME'}  = $data[1];
    $dbrecord{'LAST_NAME'}  = $data[2];
    $dbrecord{'FIRST_NAME'} = $data[3];
    $dbrecord{'ACCT_CLASS'} = $data[4];
    $dbrecord{'INSERT_TS'}  = $data[5];
    $dbrecord{'CHANGE_TS'}  = $data[6];

    # grab the person_id from the result set
    if ( defined($dbrecord{'PERSON_ID'}) ) {
        $found = 1;
    } else {
        $found = 0;
    }


    # bye.
    $sth->finish();
    $dbh->disconnect();

    if ( $found ) {
        debug("$name: returning");
        return(%dbrecord);
    } else {
        debug("$name: returning FALSE");
        return(FALSE);
    }


}


# ==============================================================================
# NAME        : getDBPersonID
# DESCRIPTION : return the person_id in the database for a given login
# ARGUMENTS   : string(login)
# RETURN      : string(personid) or FALSE
# NOTES       : fields in view are
#             : person_id, user_name, last_name, first_name,
#             : acct_class, insert_ts, change_ts
# ==============================================================================
sub getDBPersonID {
    my ($login) = @_;
    my $name = "getDBPersonID";
    my ( $package, $filename, $line ) = caller; 
    debug("$name: entering with args @_");
    debug("$name: called from package->$package, filename->$filename, line->$line");      

    my $found;
    my $personid;

    # set this just in case it's not, otherwise DBD::Ingres fails
    $ENV{"II_SYSTEM"} = "/opt";

    my $vnode  = $CFG->param("database.vnode");
    my $dbname = $CFG->param("database.dbname");
    my $view   = $CFG->param("database.acctview");
    my $dbconn = "DBI:Ingres:" . $vnode . "::" . $dbname;

    debug("$name: using dbconn string -> $dbconn");

    debug("$name: attempting to connect to database");
    my $dbh = DBI->connect( $dbconn ) || die "database connect failed: $DBI::errstr";

    my $sql = qq(SELECT DISTINCT person_id FROM $view WHERE user_name = ?);
    debug("$name: preparing sql statement");
    debug("$name: $sql");

    my $sth = $dbh->prepare($sql) or die("failed on sql prepare: $DBI::errstr");

    debug("$name: executing sql (? = $login)");
    $sth->execute($login);


    my $rc = $sth->bind_col(1, \$personid);
    my @data = $sth->fetchrow_array();
    debug("$name: found " . $sth->rows() . " rows in table for query");

    # if there are more than 1 rows, shit out
    if ( $sth->rows() > 1 ) {
        print "asshole, too many rows from query!\n";
        return(FALSE);
    } 

    # grab the person_id from the result set
    if ( defined($personid) ) {
        $found = 1;
    } else {
        $found = 0;
    }


    # bye.
    $sth->finish();
    $dbh->disconnect();

    if ( $found ) {
        debug("$name: returning with val $personid");
        return($personid);
    } else {
        debug("$name: returning FALSE");
        return(FALSE);
    }
}



# ==============================================================================
# NAME        : 
# DESCRIPTION : 
# ARGUMENTS   : 
# RETURN      : 
# NOTES       : fields in view are
#             : person_id, user_name, last_name, first_name,
#             : acct_class, insert_ts, change_ts
# ==============================================================================
sub getDBAccountClass {
    my ($login) = @_;
    my $name = "getDBAccountClass";
    my ( $package, $filename, $line ) = caller; 
    debug("$name: entering with args @_");
    debug("$name: called from package->$package, filename->$filename, line->$line");      

    my $found;
    my $class;

    # set this just in case it's not, otherwise DBD::Ingres fails
    $ENV{"II_SYSTEM"} = "/opt";

    my $vnode  = $CFG->param("database.vnode");
    my $dbname = $CFG->param("database.dbname");
    my $view   = $CFG->param("database.acctview");
    my $dbconn = "DBI:Ingres:" . $vnode . "::" . $dbname;

    debug("$name: using dbconn string -> $dbconn");

    debug("$name: attempting to connect to database");
    my $dbh = DBI->connect( $dbconn ) || die "database connect failed: $DBI::errstr";

    my $sql = qq(SELECT DISTINCT acct_class FROM $view WHERE user_name = ?);
    debug("$name: preparing sql statement");
    debug("$name: $sql");

    my $sth = $dbh->prepare($sql) or die("failed on sql prepare: $DBI::errstr");

    debug("$name: executing sql (? = $login)");
    $sth->execute($login);


    my $rc = $sth->bind_col(1, \$class);
    my @data = $sth->fetchrow_array();
    debug("$name: found " . $sth->rows() . " rows in table for query");

    # if there are more than 1 rows, shit out
    if ( $sth->rows() > 1 ) {
        print "asshole, too many rows from query!\n";
        return(FALSE);
    } 

    # grab the class from the result set
    if ( defined($class) ) {
        $found = 1;
    } else {
        $found = 0;
    }


    # bye.
    $sth->finish();
    $dbh->disconnect();

    if ( $found ) {
        debug("$name: returning with val $class");
        return($class);
    } else {
        debug("$name: returning FALSE");
        return(FALSE);
    }


}


1;
